﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common; 

namespace Inaction.Business.Security
{
    [Serializable]
    public class QueryColumnCommentCommand : Inaction.SqlCommand 
    {
        string table;
        List<string> columns;

        DataTable data = null;

        public DataTable Data
        {
            get { return data; }
        }

        public QueryColumnCommentCommand(string table="")
        {
            this.table = table;
            columns = new List<string>();
        }
        protected override void DoExecute(IDbConnection connection)
        {
            string sqlStr = @"
SELECT DISTINCT USER_TAB_COLUMNS.TABLE_NAME,
                USER_TAB_COLUMNS.COLUMN_NAME,
                USER_COL_COMMENTS.COMMENTS
  FROM USER_TAB_COLUMNS
  JOIN USER_TABLES
    ON USER_TAB_COLUMNS.TABLE_NAME = USER_TABLES.TABLE_NAME
  JOIN USER_COL_COMMENTS
    ON USER_TAB_COLUMNS.COLUMN_NAME = USER_COL_COMMENTS.COLUMN_NAME
   AND USER_TAB_COLUMNS.TABLE_NAME = USER_COL_COMMENTS.TABLE_NAME
 WHERE USER_TAB_COLUMNS.COLUMN_NAME not like ('%_ID')
   AND USER_TAB_COLUMNS.COLUMN_NAME not like ('%_INPUTER')
   AND USER_TAB_COLUMNS.COLUMN_NAME not like ('%_INPUTTIME')
  " + (string.IsNullOrEmpty(table) ? string.Empty : " AND USER_TABLES.TABLE_NAME = '" + table + "'") + @"
 ORDER BY USER_TAB_COLUMNS.COLUMN_NAME";
            using (var command = connection.CreateCommand())
            {
                command.CommandText = sqlStr;
                data = new DataTable();
                data.Columns.Add("TABLE_NAME");
                data.Columns.Add("COLUMN_NAME");
                data.Columns.Add("COMMENTS");
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    var row = data.NewRow();
                    row[0] = reader[0];
                    row[1] = reader[1];
                    row[2] = reader[2];
                    data.Rows.Add(row);
                }
            }
        }
    }
}
